﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GFMS.MODEL;
using KPIS.GERP.GFMS.IDAL;

namespace KPIS.GERP.GFMS.DAL
{
    public sealed class D_MessageReply : IDAL_MessageReply
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        ConvertNullable cn = new ConvertNullable();
        

        public D_MessageReply()
        {

        }

        public IList<M_MessageReply> GetFull(Nullable<int> to_emp_seq
            , Nullable<int> from_emp_seq, string message_status)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_MessageReply> infoList = new List<M_MessageReply>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@to_emp_seq", to_emp_seq);
                dbManager.AddParameters(2, "@from_emp_seq", from_emp_seq);
                dbManager.AddParameters(3, "@message_status", message_status);

                strSQL = "SELECT re.reply_seq, re.reply_date, re.ref_no, re.reply_text"
                    + ", re.from_emp_seq, re.to_emp_seq, re.from_send_seq"
                    + ", re.message_status, re.reply_type"
                    + ", user1.user_name as from_user_name, user2.user_name as to_user_name"
                    + ", CASE WHEN re.reply_type = '0' THEN 'ไม่เห็นชอบ'"
                        + " ELSE CASE WHEN re.reply_type = '1' THEN 'เห็นชอบ'"
                            + " ELSE CASE WHEN re.reply_type = '2' THEN 'ส่งต่อ'"
                                + " ELSE 'รอการพิจารณา'"
                                + " END"
                            + " END"
                        + " END AS reply_type_name"

                    + ", re.remarks, re.record_status"
                    + ", re.created_by, re.created_when, re.updated_by, re.updated_when"
                    + " FROM message_reply re"
                    + " LEFT JOIN gerp_security.users user1 ON re.from_emp_seq = user1.user_seq"
                    + " LEFT JOIN gerp_security.users user2 ON re.to_emp_seq = user2.user_seq"
                    + " WHERE re.record_status not like @delete_status"
                    + (to_emp_seq != null ? " AND re.to_emp_seq = @to_emp_seq" : "")
                    + (from_emp_seq != null ? " AND re.from_emp_seq = @from_emp_seq" : "")
                    + (message_status != null ? " AND re.message_status = @message_status" : "")
                    + " ORDER BY re.reply_date";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_MessageReply info = new M_MessageReply();
                        info.reply_seq = Convert.ToInt32(drc[i]["reply_seq"]);
                        info.reply_date = cn.ConvertNullDateTime(drc[i]["reply_date"]);
                        info.ref_no = drc[i]["ref_no"].ToString();
                        info.reply_text = drc[i]["reply_text"].ToString();
                        info.from_emp_seq = cn.ConvertNullInt(drc[i]["from_emp_seq"]);
                        info.to_emp_seq = cn.ConvertNullInt(drc[i]["to_emp_seq"]);
                        info.reply_type = drc[i]["reply_type"].ToString();
                        info.from_send_seq = cn.ConvertNullInt(drc[i]["from_send_seq"]);
                        info.from_user_name = drc[i]["from_user_name"].ToString();
                        info.to_user_name = drc[i]["to_user_name"].ToString();
                        info.reply_type_name = drc[i]["reply_type_name"].ToString();
                        info.remarks = drc[i]["remarks"].ToString();
                        info.message_status = drc[i]["message_status"].ToString();

                        info.record_status = drc[i]["record_status"].ToString();
                        info.created_by = cn.ConvertNullInt(drc[i]["created_by"]);
                        info.created_when = cn.ConvertNullDateTime(drc[i]["created_when"]);
                        info.updated_by = cn.ConvertNullInt(drc[i]["updated_by"]);
                        info.updated_when = cn.ConvertNullDateTime(drc[i]["updated_when"]);
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_MessageReply> GetList(Nullable<int> to_emp_seq
            , Nullable<int> from_emp_seq, string message_status)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_MessageReply> infoList = new List<M_MessageReply>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@to_emp_seq", to_emp_seq);
                dbManager.AddParameters(2, "@from_emp_seq", from_emp_seq);
                dbManager.AddParameters(3, "@message_status", message_status);

                strSQL = "SELECT re.reply_seq, re.reply_text, re.reply_date"
                    + " FROM message_reply re"
                    + " WHERE re.record_status not like @delete_status"
                    + (to_emp_seq != null ? " AND re.to_emp_seq = @to_emp_seq" : "")
                    + (from_emp_seq != null ? " AND re.from_emp_seq = @from_emp_seq" : "")
                    + (message_status != null ? " AND re.message_status = @message_status" : "")
                    + " ORDER BY re.reply_date";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_MessageReply info = new M_MessageReply();
                        info.reply_seq = Convert.ToInt32(drc[i]["reply_seq"]);
                        info.reply_text = drc[i]["reply_text"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public M_MessageReply GetInfo(int seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_MessageReply info = new M_MessageReply();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@seq", seq);

                strSQL = "SELECT re.reply_seq, re.reply_date, re.ref_no, re.reply_text"
                    + ", re.from_emp_seq, re.to_emp_seq, re.from_send_seq"
                    + ", re.message_status, re.reply_type"
                    + ", user1.user_name as from_user_name, user2.user_name as to_user_name"
                    + ", CASE WHEN re.reply_type = '0' THEN 'ไม่เห็นชอบ'"
                        + " ELSE CASE WHEN re.reply_type = '1' THEN 'เห็นชอบ'"
                            + " ELSE CASE WHEN re.reply_type = '2' THEN 'ส่งต่อ'"
                                + " ELSE 'รอการพิจารณา'"
                                + " END"
                            + " END"
                        + " END AS reply_type_name"

                    + ", re.remarks, re.record_status"
                    + ", re.created_by, re.created_when, re.updated_by, re.updated_when"
                    + " FROM message_reply re"
                    + " LEFT JOIN gerp_security.users user1 ON re.from_emp_seq = user1.user_seq"
                    + " LEFT JOIN gerp_security.users user2 ON re.to_emp_seq = user2.user_seq"
                    + " WHERE re.reply_seq = @seq";
                

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.reply_seq = Convert.ToInt32(drc[0]["reply_seq"]);
                    info.reply_date = cn.ConvertNullDateTime(drc[0]["reply_date"]);
                    info.ref_no = drc[0]["ref_no"].ToString();
                    info.reply_text = drc[0]["reply_text"].ToString();
                    info.from_emp_seq = cn.ConvertNullInt(drc[0]["from_emp_seq"]);
                    info.to_emp_seq = cn.ConvertNullInt(drc[0]["to_emp_seq"]);
                    info.reply_type = drc[0]["reply_type"].ToString();
                    info.from_send_seq = cn.ConvertNullInt(drc[0]["from_send_seq"]);
                    info.from_user_name = drc[0]["from_user_name"].ToString();
                    info.to_user_name = drc[0]["to_user_name"].ToString();
                    info.reply_type_name = drc[0]["reply_type_name"].ToString();
                    info.remarks = drc[0]["remarks"].ToString();
                    info.message_status = drc[0]["message_status"].ToString();

                    info.record_status = drc[0]["record_status"].ToString();
                    info.created_by = cn.ConvertNullInt(drc[0]["created_by"]);
                    info.created_when = cn.ConvertNullDateTime(drc[0]["created_when"]);
                    info.updated_by = cn.ConvertNullInt(drc[0]["updated_by"]);
                    info.updated_when = cn.ConvertNullDateTime(drc[0]["updated_when"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public IList<M_MessageReply> GetInfoFromMoneyReqSeq(int seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_MessageReply> infoList = new List<M_MessageReply>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@seq", seq);
                dbManager.AddParameters(1, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT re.reply_seq, re.reply_date, re.ref_no, re.reply_text"
                    + ", re.from_emp_seq, re.to_emp_seq, re.from_send_seq"
                    + ", re.message_status, re.reply_type"
                    + ", cast(concat(np1.prefix_name, emp1.first_name, ' ', emp1.last_name) as varchar) as from_user_name"
                    + ", cast(concat(np2.prefix_name, emp2.first_name, ' ', emp2.last_name) as varchar) as to_user_name"
                    + ", pos1.pos_name as from_user_pos_name, pos2.pos_name as to_user_pos_name"
                    + ", CASE WHEN re.reply_type = '0' THEN 'ไม่เห็นชอบ'"
                        + " ELSE CASE WHEN re.reply_type = '1' THEN 'เห็นชอบ'"
                            + " ELSE CASE WHEN re.reply_type = '2' THEN 'ส่งต่อ'"
                                + " ELSE 'รอการพิจารณา'"
                                + " END"
                            + " END"
                        + " END AS reply_type_name"

                    + ", re.remarks, re.record_status"
                    + ", re.created_by, re.created_when, re.updated_by, re.updated_when"
                    + " FROM message_reply re"
                    + " LEFT JOIN message_send send ON re.from_send_seq = send.send_seq"
                    + " LEFT JOIN hrm_ps_employees emp1 ON re.from_emp_seq = emp1.emp_seq"
                    + " LEFT JOIN hrm_ps_employees emp2 ON re.to_emp_seq = emp2.emp_seq"
                    + " LEFT JOIN hrm_mp_positions pos1 ON emp1.pos_seq = pos1.pos_seq"
                    + " LEFT JOIN hrm_mp_positions pos2 ON emp2.pos_seq = pos2.pos_seq"
                    + " LEFT JOIN mas_name_prefixes np1 ON emp1.name_prefix_seq = np1.name_prefix_seq"
                    + " LEFT JOIN mas_name_prefixes np2 ON emp2.name_prefix_seq = np2.name_prefix_seq"
                    + " WHERE send.ref_seq = @seq"
                    + " AND send.record_status not like @delete_status"
                    + " AND re.record_status not like @delete_status"
                    + " AND send.ref_type = 'fim_money_requests'";
                

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_MessageReply info = new M_MessageReply();
                        info.reply_seq = Convert.ToInt32(drc[i]["reply_seq"]);
                        info.reply_date = cn.ConvertNullDateTime(drc[i]["reply_date"]);
                        info.ref_no = drc[i]["ref_no"].ToString();
                        info.reply_text = drc[i]["reply_text"].ToString();
                        info.from_emp_seq = cn.ConvertNullInt(drc[i]["from_emp_seq"]);
                        info.to_emp_seq = cn.ConvertNullInt(drc[i]["to_emp_seq"]);
                        info.reply_type = drc[i]["reply_type"].ToString();
                        info.from_send_seq = cn.ConvertNullInt(drc[i]["from_send_seq"]);
                        info.from_user_name = drc[i]["from_user_name"].ToString();
                        info.to_user_name = drc[i]["to_user_name"].ToString();
                        info.from_user_pos_name = drc[i]["from_user_pos_name"].ToString();
                        info.to_user_pos_name = drc[i]["to_user_pos_name"].ToString();
                        info.reply_type_name = drc[i]["reply_type_name"].ToString();
                        info.remarks = drc[i]["remarks"].ToString();
                        info.message_status = drc[i]["message_status"].ToString();

                        info.record_status = drc[i]["record_status"].ToString();
                        info.created_by = cn.ConvertNullInt(drc[i]["created_by"]);
                        info.created_when = cn.ConvertNullDateTime(drc[i]["created_when"]);
                        info.updated_by = cn.ConvertNullInt(drc[i]["updated_by"]);
                        info.updated_when = cn.ConvertNullDateTime(drc[i]["updated_when"]);
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public M_MessageReply GetInfoFromSendSeq(int seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_MessageReply info = new M_MessageReply();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@seq", seq);
                dbManager.AddParameters(1, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT re.reply_seq, re.reply_date, re.ref_no, re.reply_text"
                    + ", re.from_emp_seq, re.to_emp_seq, re.from_send_seq"
                    + ", re.message_status, re.reply_type"
                    + ", user1.user_name as from_user_name, user2.user_name as to_user_name"
                    + ", CASE WHEN re.reply_type = '0' THEN 'ไม่เห็นชอบ'"
                        + " ELSE CASE WHEN re.reply_type = '1' THEN 'เห็นชอบ'"
                            + " ELSE CASE WHEN re.reply_type = '2' THEN 'ส่งต่อ'"
                                + " ELSE 'รอการพิจารณา'"
                                + " END"
                            + " END"
                        + " END AS reply_type_name"

                    + ", re.remarks, re.record_status"
                    + ", re.created_by, re.created_when, re.updated_by, re.updated_when"
                    + " FROM message_reply re"
                    + " LEFT JOIN gerp_security.users user1 ON re.from_emp_seq = user1.user_seq"
                    + " LEFT JOIN gerp_security.users user2 ON re.to_emp_seq = user2.user_seq"
                    + " WHERE re.from_send_seq = @seq"
                    + " AND re.record_status not like @delete_status";
                

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.reply_seq = Convert.ToInt32(drc[0]["reply_seq"]);
                    info.reply_date = cn.ConvertNullDateTime(drc[0]["reply_date"]);
                    info.ref_no = drc[0]["ref_no"].ToString();
                    info.reply_text = drc[0]["reply_text"].ToString();
                    info.from_emp_seq = cn.ConvertNullInt(drc[0]["from_emp_seq"]);
                    info.to_emp_seq = cn.ConvertNullInt(drc[0]["to_emp_seq"]);
                    info.reply_type = drc[0]["reply_type"].ToString();
                    info.from_send_seq = cn.ConvertNullInt(drc[0]["from_send_seq"]);
                    info.from_user_name = drc[0]["from_user_name"].ToString();
                    info.to_user_name = drc[0]["to_user_name"].ToString();
                    info.reply_type_name = drc[0]["reply_type_name"].ToString();
                    info.remarks = drc[0]["remarks"].ToString();
                    info.message_status = drc[0]["message_status"].ToString();

                    info.record_status = drc[0]["record_status"].ToString();
                    info.created_by = cn.ConvertNullInt(drc[0]["created_by"]);
                    info.created_when = cn.ConvertNullDateTime(drc[0]["created_when"]);
                    info.updated_by = cn.ConvertNullInt(drc[0]["updated_by"]);
                    info.updated_when = cn.ConvertNullDateTime(drc[0]["updated_when"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        //public string SelectRecentInsertedRoleSeq()
        //{
        //    string strSQL;
        //    string strResult;

        //    try
        //    {
        //        dbManager.Open();

        //        strSQL = "select scope_identity() as last_seq";

        //        strResult = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0]["last_seq"].ToString();
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }

        //    return strResult;
        //}

        public string Insert(M_MessageReply info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(13);

                dbManager.AddParameters(0, "@reply_seq", info.reply_seq);
                dbManager.AddParameters(1, "@reply_date", info.reply_date);
                dbManager.AddParameters(2, "@ref_no", info.ref_no);
                dbManager.AddParameters(3, "@reply_text", info.reply_text);
                dbManager.AddParameters(4, "@reply_type", info.reply_type);
                dbManager.AddParameters(5, "@from_emp_seq", info.from_emp_seq);
                dbManager.AddParameters(6, "@to_emp_seq", info.to_emp_seq);
                dbManager.AddParameters(7, "@from_send_seq", info.from_send_seq);
                dbManager.AddParameters(8, "@remarks", info.remarks);
                dbManager.AddParameters(9, "@message_status", info.message_status);

                dbManager.AddParameters(10, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(11, "@created_by", info.created_by);
                dbManager.AddParameters(12, "@created_when", info.created_when);

                strSQL = "INSERT INTO message_reply"
                    + "(reply_date, ref_no, reply_text, reply_type"
                    + ", from_emp_seq, to_emp_seq, from_send_seq, remarks, message_status"
                    + ", record_status, created_by, created_when)"
                    + " VALUES(@reply_date"
                        + ", @ref_no"
                        + ", @reply_text"
                        + ", @reply_type"
                        + ", @from_emp_seq"
                        + ", @to_emp_seq"
                        + ", @from_send_seq"
                        + ", @remarks"
                        + ", @message_status"

                        + ", @record_status"
                        + ", @created_by"
                        + ", @created_when);"
                    + " select scope_identity();";
                var strSeq = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0][0].ToString();
                return strSeq;
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(M_MessageReply info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(12);

                dbManager.AddParameters(0, "@reply_seq", info.reply_seq);
                dbManager.AddParameters(1, "@reply_date", info.reply_date);
                dbManager.AddParameters(2, "@ref_no", info.ref_no);
                dbManager.AddParameters(3, "@reply_text", info.reply_text);
                dbManager.AddParameters(4, "@reply_type", info.reply_type);
                dbManager.AddParameters(5, "@from_emp_seq", info.from_emp_seq);
                dbManager.AddParameters(6, "@to_emp_seq", info.to_emp_seq);
                dbManager.AddParameters(7, "@from_send_seq", info.from_send_seq);
                dbManager.AddParameters(8, "@remarks", info.remarks);
                dbManager.AddParameters(9, "@message_status", info.message_status);

                dbManager.AddParameters(10, "@updated_by", info.updated_by);
                dbManager.AddParameters(11, "@updated_when", info.updated_when);

                strSQL = "UPDATE message_reply"
                    + " set reply_date = @reply_date"
                    + ", ref_no = @ref_no"
                    + ", reply_text = @reply_text"
                    + ", reply_type = @reply_type"
                    + ", from_emp_seq = @from_emp_seq"
                    + ", to_emp_seq = @to_emp_seq"
                    + ", from_send_seq = @from_send_seq"
                    + ", remarks = @remarks"
                    + ", message_status = @message_status"

                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE reply_seq = @reply_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(M_MessageReply info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@reply_seq", info.reply_seq);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", info.updated_by);
                dbManager.AddParameters(3, "@updated_when", info.updated_when);

                strSQL = "UPDATE message_reply"
                    + " SET record_status = @record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE reply_seq = @reply_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateMessageStatus(M_MessageReply info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@reply_seq", info.reply_seq);
                dbManager.AddParameters(1, "@message_status", info.message_status);
                dbManager.AddParameters(2, "@updated_by", info.updated_by);
                dbManager.AddParameters(3, "@updated_when", info.updated_when);

                strSQL = "UPDATE message_reply"
                    + " SET message_status = @message_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE reply_seq = @reply_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
